﻿using Anke.SHManage.Model;
using Anke.SHManage.Model.FixedAssetsModel;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL.FixedAssetsDAL
{
    /// <summary>
    /// 固定资产基础信息列表 DAL
    /// plq
    /// </summary>
    public class FixedAssetsMaterialDAL
    {
        /// <summary>
        /// 获取固定资产信息列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="assetName"></param>
        /// <param name="assetID"></param>
        /// <param name="assetTypeID"></param>
        /// <param name="isScrap"></param>
        /// <param name="assetBrand"></param>
        /// <param name="assetCurStorageID"></param>
        /// <param name="assetManageDep"></param>
        /// <param name="ambulanceNum"></param>
        /// <returns></returns>
        public object GetAssetsList(int pageSize, int pageIndex, string assetName, string assetID, string assetTypeID, bool isScrap, string assetBrand, int assetCurStorageID, string assetManageDep, string ambulanceNum)
        {
            List<FIXED_MatetialInfo> list = new List<FIXED_MatetialInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select distinct identity(int,1,1) as 行号 ");
            sb.Append(" ,fm.编码,fm.固定资产编码,fm.类型编码,fm.资产名称,fm.主机序列号,fm.品牌,fm.型号,fm.规格 ");
            sb.Append(" ,fm.单位,fm.数量,fm.发票号码,fm.单价,fm.购建日期,fm.入库日期,fm.当前仓库编码,fm.当前存放点 ");
            sb.Append(" ,fm.当前车牌号码,fm.资产管理部门,fm.资产管理人,fm.备注,fm.是否报废 ");
            sb.Append(" ,类型=td.Name ");
            sb.Append(" ,当前仓库=fs.仓库名称 ");
            sb.Append(" into #temp ");
            sb.Append(@"   
                from FIXED_Matetial fm ");
            sb.Append(@"   
                left join TDictionary td on td.ID=fm.类型编码");
            sb.Append(@"   
                left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码");
            sb.Append(@"
                where 1=1 ");
            WhereClauseUtility.AddStringLike("资产名称", assetName, sb);
            WhereClauseUtility.AddStringEqual("固定资产编码", assetID, sb);
            
            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", assetTypeID, sb);
            if ((assetTypeID.Length > 0) && (assetTypeID != null) && (assetTypeID != "-1") && (assetTypeID != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(assetTypeID); //分类编码list
                //string s1 = string.Join(",", typeIDList.ToArray()); //将list转为string，逗号分隔
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("类型编码", s1, sb);
            }

            if (isScrap)
            {
                sb.Append(" and fm.是否报废=1");
            }
            else
            {
                sb.Append(" and fm.是否报废=0");
            }
            WhereClauseUtility.AddStringLike("fm.品牌", assetBrand, sb);
            
            //仓库(使用部门/分站)筛选
            //WhereClauseUtility.AddIntEqual("当前仓库编码", assetCurStorageID, sb);
            if (assetCurStorageID >= 0)
            {
                List<int> storageIDList = GetStorageIntListByID(assetCurStorageID);//仓库编码list
                string s2 = string.Join(",", storageIDList.ToArray()); //将list转为string，逗号分隔
                WhereClauseUtility.AddInSelectQuery("当前仓库编码", s2, sb);
            }
         
            WhereClauseUtility.AddStringLike("资产管理部门", assetManageDep, sb);
            WhereClauseUtility.AddStringEqual("当前车牌号码", ambulanceNum, sb);

            sb.Append(@"
                order by 入库日期,购建日期,资产名称 ");
            sb.Append(@"
                    select top " + pageSize + " A.*  from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
            sb.Append(@"
                    SELECT count(*) FROM #temp t
                    drop table #temp ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_MatetialInfo info = new FIXED_MatetialInfo();
                        info.BianMa = new Guid(dr["编码"].ToString());
                        info.GuDingZiChanBianMa = dr["固定资产编码"].ToString();
                        info.LeiXingBianMa = dr["类型编码"].ToString();
                        info.LeiXing = dr["类型"].ToString();
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();
                        info.PinPai = dr["品牌"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.DanWei = dr["单位"].ToString();
                        info.ShuLiang = dr["数量"].ToString();
                        info.FaPiaoHaoMa = dr["发票号码"].ToString();
                        info.DanJia = Convert.ToDouble(dr["单价"].ToString());
                        info.GouJianRiQi = Convert.ToDateTime(dr["购建日期"].ToString());
                        info.RuKuRiQi = Convert.ToDateTime(dr["入库日期"].ToString());
                        info.DangQianCangKuBianMa = Convert.ToInt32(dr["当前仓库编码"].ToString());
                        info.DangQianCangKu = dr["当前仓库"].ToString();
                        info.DangQianCunFangDian = dr["当前存放点"].ToString();
                        info.DangQianChePaiHaoMa = dr["当前车牌号码"].ToString();
                        info.ZiChanGuanLiBuMen = dr["资产管理部门"].ToString();
                        info.ZiChanGuanLiRen = dr["资产管理人"].ToString();
                        info.BeiZhu = dr["备注"].ToString();
                        info.ShiFouBaoFei = Convert.ToBoolean(dr["是否报废"]);
                       
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/GetAssetsList", ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 递归查询该分类ID下的所有list（分类及后代分类）-适用于树结构数据
        /// </summary>
        /// <param name="assetTypeID"></param>
        /// <returns></returns>
        private List<string> GetTDicStrListByID(string assetTypeID)
        {
            List<string> list = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with dic 
                            as( select * 
                                from TDictionary 
                                where ID in ('" + assetTypeID + @"')
                                union all 
                                select TDictionary.* 
                                from dic , TDictionary 
                                where dic.id = TDictionary.ParentID
                            )
                          select * from dic");
            try
            {
               DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
               if (ds.Tables[0].Rows.Count > 0)
               {
                   foreach (DataRow dr in ds.Tables[0].Rows)
                   {
                       list.Add(dr["ID"].ToString());
                   }
               }
               return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/GetTDicStrListByID", ex.Message);
                return null;
            }
           
        }

        /// <summary>
        /// 递归查询该仓库编码下的所有list（仓库及所有后代仓库）--适用于树结构数据
        /// </summary>
        /// <param name="assetCurStorageID"></param>
        /// <returns></returns>
        private List<int> GetStorageIntListByID(int assetCurStorageID)
        {
            List<int> list = new List<int>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with temp 
                            as( select * 
                                from FIXED_Storage 
                                where 仓库编码 in (" + assetCurStorageID + @")
                                union all 
                                select FIXED_Storage.* 
                                from temp , FIXED_Storage 
                                where temp.仓库编码 = FIXED_Storage.父级仓库编码
                            )
                          select * from temp");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        list.Add(Convert.ToInt32(dr["仓库编码"]));
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/GetStorageIntListByID", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增固定资产
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool InsertEditPageData(FIXED_MatetialInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            info.BianMa = Guid.NewGuid();
            sb.Append(" insert into FIXED_Matetial values (");
            sb.Append(" '" + info.BianMa + "', ");
            sb.Append(" '" + info.GuDingZiChanBianMa + "', ");
            sb.Append(" '" + info.LeiXingBianMa + "', ");
            sb.Append(" '" + info.ZiChanMingCheng + "', ");
            sb.Append(" '" + info.ZhuJiXuLieHao + "', ");
            sb.Append(" '" + info.PinPai + "', ");
            sb.Append(" '" + info.XingHao + "', ");
            sb.Append(" '" + info.GuiGe + "', ");
            sb.Append(" '" + info.DanWei + "', ");
            sb.Append(" '" + info.ShuLiang + "', ");
            sb.Append(" '" + info.FaPiaoHaoMa + "', ");
            sb.Append(" " + info.DanJia + ", ");
            sb.Append(" '" + info.GouJianRiQi + "', ");
            sb.Append(" '" + info.RuKuRiQi + "', ");
            sb.Append(" " + info.DangQianCangKuBianMa + ", ");
            sb.Append(" '" + info.DangQianCunFangDian + "', ");
            sb.Append(" '" + info.DangQianChePaiHaoMa + "', ");
            sb.Append(" '" + info.ZiChanGuanLiBuMen + "', ");
            sb.Append(" '" + info.ZiChanGuanLiRen + "', ");
            sb.Append(" '" + info.BeiZhu + "' ");
            if (info.ShiFouBaoFei)
            {
                sb.Append(", 1 ");
            }
            else {
                sb.Append(", 0 ");
            }
            sb.Append(" )");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/InsertEditPageData", ex.Message);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 判断是否已有对应资产数据
        /// </summary>
        /// <param name="code">固定资产编码</param>
        /// <returns></returns>
        public bool IsHaveCurrentData(string code)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" select * ");
            sb.Append(@"   
                from FIXED_Matetial fm ");
            sb.Append(@"
                where 1=1 ");
            WhereClauseUtility.AddStringEqual("固定资产编码", code, sb);
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return ds.Tables[0].Rows.Count>0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/IsHaveCurrentData", ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 获取编辑页初始数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public FIXED_MatetialInfo GetEditPageData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            Guid Code1=new Guid(Code);
            sb.Append(@" select fm.* ");
            sb.Append(@" from FIXED_Matetial fm ");
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(" and 编码='" + Code1 + "'");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                DataTable dt = ds.Tables[0];
                var query = from a in dt.AsEnumerable()
                            select new FIXED_MatetialInfo()
                            {
                                BianMa = a.Field<Guid>("编码"),
                                GuDingZiChanBianMa = a.Field<string>("固定资产编码"),
                                LeiXingBianMa = a.Field<string>("类型编码"),
                                ZiChanMingCheng = a.Field<string>("资产名称"),
                                ZhuJiXuLieHao = a.Field<string>("主机序列号"),
                                PinPai = a.Field<string>("品牌"),
                                XingHao = a.Field<string>("型号"),
                                GuiGe = a.Field<string>("规格"),
                                DanWei = a.Field<string>("单位"),
                                ShuLiang = a.Field<string>("数量"),
                                FaPiaoHaoMa = a.Field<string>("发票号码"),
                                DanJia = a.Field<Double>("单价"),
                                GouJianRiQi = a.Field<DateTime>("购建日期"),
                                RuKuRiQi = a.Field<DateTime>("入库日期"),
                                DangQianCangKuBianMa = a.Field<int>("当前仓库编码"),
                                DangQianCunFangDian = a.Field<string>("当前存放点"),
                                DangQianChePaiHaoMa = a.Field<string>("当前车牌号码"),
                                ZiChanGuanLiBuMen = a.Field<string>("资产管理部门"),
                                ZiChanGuanLiRen = a.Field<string>("资产管理人"),
                                BeiZhu = a.Field<string>("备注"),
                                ShiFouBaoFei = a.Field<Boolean>("是否报废"),
                            };
                var result = query.FirstOrDefault();
                return result;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/GetEditPageData", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 保存编辑固定资产
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateEditPageData(FIXED_MatetialInfo info)
        {
            bool result = true;
            StringBuilder sb = new StringBuilder();
            sb.Append(" update FIXED_Matetial set ");
            sb.Append(@" 
                        固定资产编码='" + info.GuDingZiChanBianMa + "', ");
            sb.Append(" 类型编码='" + info.LeiXingBianMa + "', ");
            sb.Append(" 资产名称='" + info.ZiChanMingCheng + "', ");
            sb.Append(" 主机序列号='" + info.ZhuJiXuLieHao + "', ");
            sb.Append(" 品牌='" + info.PinPai + "', ");
            sb.Append(" 型号='" + info.XingHao + "', ");
            sb.Append(" 规格='" + info.GuiGe + "', ");
            sb.Append(" 单位='" + info.DanWei + "', ");
            sb.Append(" 数量='" + info.ShuLiang + "', ");
            sb.Append(" 发票号码='" + info.FaPiaoHaoMa + "', ");
            sb.Append(" 单价=" + info.DanJia + ", ");
            sb.Append(" 购建日期='" + info.GouJianRiQi + "',");
            sb.Append(" 入库日期='" + info.RuKuRiQi + "',");
            sb.Append(" 当前仓库编码=" + info.DangQianCangKuBianMa + ", ");
            sb.Append(" 当前存放点='" + info.DangQianCunFangDian + "',");
            sb.Append(" 当前车牌号码='" + info.DangQianChePaiHaoMa + "',");
            sb.Append(" 资产管理部门='" + info.ZiChanGuanLiBuMen + "',");
            sb.Append(" 资产管理人='" + info.ZiChanGuanLiRen + "',");
            sb.Append(" 备注='" + info.BeiZhu + "',");
            if (info.ShiFouBaoFei)
            {
                sb.Append(" 是否报废=1 " );
            }
            else
            {
                sb.Append(" 是否报废=0 ");
            }
            sb.Append(@" 
                        where 1=1 ");
            sb.Append(@" 
                        and 编码= '"+info.BianMa+"'");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/UpdateEditPageData", ex.Message);
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 删除单条数据
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public bool DeleteData(string Code)
        {
            StringBuilder sb = new StringBuilder();
            Guid Code1 = new Guid(Code);
            sb.Append(" delete from FIXED_Matetial ");
            sb.Append(" where 1=1 ");
            sb.Append(" and 编码='" + Code1 + "'");
            try
            {
                int intRow = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                return intRow > 0;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsMaterialDAL/DeleteData", ex.Message);
                return false;
            }
        }
    }
}
